今更聞けない、Looker派生テーブルの永続化について改めて理解する #looker

モダンデータスタック(MDS)

#Looker

ikumi

2024.03.14

Lookerの機能の一つである派生テーブルはご存じでしょうか？派生テーブルには2種類あり、前回は一時的派生テーブルについて紹介しました。

今回は、永続的派生テーブルについて詳しく紹介していきます。

永続的派生テーブル（PDT）とは

永続的派生テーブルはPDT（Persistent Derived Table）とも呼ばれる、データベースに直接書き込まれ、指定したスケジュールで再生成される派生テーブルのことです。PDTにおいても、『SQL派生テーブル』『ネイティブ派生テーブル』のいずれかで定義します。

PDTの作り方

PDTを作成するには、以下の設定が必要になります。

PDTに対応しているデータベースで、PDTを有効にする
PDTの書き込み用のスキーマを指定する
派生テーブルを永続化する

PDTの有効化と書き込み用のスキーマ指定

まずはConnetionの設定画面を開き、PDTを有効にするをONにします
また、データベース側で作成した、PDT書き込み用にスキーマを一時データベースに記載します

PDTの再生成ポリシーを適用して派生テーブルを永続化する

派生テーブルに以下のパラメーターでPDTの再生成ポリシーを適用することで、永続化することができます。

datagroup_trigger
sql_trigger_value
interval_trigger
persist_for

いわゆるPDTを作成する特別な設定はなく、派生テーブルにポリシーを適用した時点で派生テーブルが永続化されます。

datagroup_trigger

クエリのキャッシュポリシーの適用方法としても用いられるdatagroupを使用することでPDTの再生成ポリシーを適用します。datagroupは、最も柔軟性の高いポリシーを設定できます。この際、サブパラメーターとしてsql_triggerまたはinterval_triggerを使用して定義した場合、datagroup_triggerパラメータを使用してPDTの再生成ルールを定義できます。

公式Doc：データグループによるクエリのキャッシングとPDTの再構築

## model ファイルで datagroup の定義
datagroup: pdt_datagroup {
  max_cache_age: "24 hours"
  sql_trigger: SELECT MAX(date) ;;
}

## view ファイルで datagroup_trigger の適用
  derived_table: {
    sql: SELECT
      date
      FROM
      `database_name.sample_superstore`
      GROUP BY 1 ;;
      datagroup_trigger: pdt_datagroup
  }

sql_trigger_value

1 つの値 (1 行と 1 列) だけを返すクエリを作成し、作成したSQLの結果に基づいてPDTの再生成をトリガーします。SQL文の結果が前の値と異なる場合、PDTが再生成されます。

##sql_trigger_value適用例  
  derived_table: {
    sql: SELECT
      date
      FROM
      `database_name.sample_superstore`
      GROUP BY 1 ;;
      sql_trigger_value: SELECT MAX(date) ;;
  }

なお、デフォルトでは、別の永続的な派生テーブルが構築中でない限り、Lookerはここで定義したSQLクエリを5分ごとに実行します。このスケジュールは、PDT およびデータグループメンテナンススケジュールで変更することができます。

interval_trigger

パラメータは、"24 hours" や "60 minutes" など、指定した時間間隔に基づいて、PDTの再生成をトリガーします。

##interval_trigger適用例  
  derived_table: {
    sql: SELECT
      date
      FROM
      `database_name.sample_superstore`
      GROUP BY 1 ;;
      interval_trigger: "24 hours"
  }

persist_for

PDTが作成されデータベースから削除されるまでの保存期間を設定します。このパラメーターを使用するとユーザーが最初にクエリを実行したときにPDTが作成され、persist_for パラメータで指定された期間PDTをデータベースに保持します。設定した期間を超過すると、LookerはデータベースからPDTを消去します。そのPDTはユーザーが次にPDTを含むクエリを実行したそのタイミングで再生成するため、予期せぬタイミングでPDTが作成されるケースもあるという点に注意が必要です。

##persist_for適用例  
  derived_table: {
    sql: SELECT
      date
      FROM
      `database_name.sample_superstore`
      GROUP BY 1 ;;
      persist_for: "24 hours"
  }